package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import ModelLayer.KeyWord;

import ModelLayer.RoomBooking;
import ModelLayer.RoomBookingLine;

public class DBRoomBooking implements IFDBRoomBooking {
	private Connection con;

	public DBRoomBooking() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public RoomBooking getRoomBooking(int roomBookingId,
			boolean retrieveAssociation) {
		String wClause = "  roomBookingId = '" + roomBookingId + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertRoomBooking(RoomBooking roomBooking) {
		int rc = -1;
		String query = "INSERT INTO roomBooking(roomBookingId, createDate, startDate, endDate, deposit, rent, payStatus) VALUES('"
				+ roomBooking.getId()
				+ "','"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getCreateDate()))
				+ "','"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getStartDate()))
				+ "','"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getEndDate()))
				+ "','"
				+ roomBooking.getDeposit()
				+ "','"
				+ roomBooking.getRent()
				+ "','"
				+ roomBooking.getPayStatus().toString() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("RoomBooking is not inserted");
		}
		return rc;
	}

	@Override
	public int updateRoomBooking(RoomBooking roomBooking) {
		RoomBooking roomBookingObj = roomBooking;
		int rc = -1;
		String query = "UPDATE roomBooking SET "
				+ "createDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getCreateDate()))
				+ "',"
				+ "startDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getStartDate()))
				+ "',"
				+ "endDate = '"
				+ (new SimpleDateFormat("dd/MM/yyyy").format(roomBooking
						.getEndDate())) + "'," + "deposit = '"
				+ roomBookingObj.getDeposit() + "'," + "payStatus = '"
				+ roomBookingObj.getPayStatus().toString() + "'," + "rent = '"
				+ roomBookingObj.getRent() + "'" + " WHERE roomBookingId = '"
				+ roomBookingObj.getId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in roomBooking db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteRoomBooking(int roomBookingId) {
		int rc = -1;
		String query = "DELETE FROM roomBooking WHERE roomBookingId = '"
				+ roomBookingId + "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in roomBooking db: " + ex);
		}
		return rc;
	}

	@Override
	public LinkedList<RoomBooking> getAllRoomBooking() {
		return miscWhere("");
	}

	private RoomBooking singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		RoomBooking roomBooking = new RoomBooking();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				roomBooking = buildRoomBooking(results);
				stmt.close();
				if (retrieveAssociation) {
					IFDBRBLine dbRBLine = new DBRBLine();
					LinkedList<RoomBookingLine> lines = dbRBLine
							.getAllLineForRB(roomBooking.getId(), true);
					roomBooking.setLines(lines);
					System.out.println("roombookinglines are selected");
				}
			} else {
				roomBooking = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return roomBooking;
	}

	private LinkedList<RoomBooking> miscWhere(String wClause) {
		ResultSet results;
		LinkedList<RoomBooking> list = new LinkedList<RoomBooking>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				RoomBooking roomBooking = new RoomBooking();
				roomBooking = buildRoomBooking(results);
				list.add(roomBooking);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private RoomBooking buildRoomBooking(ResultSet results) {
		RoomBooking roomBooking = new RoomBooking();
		try {
			roomBooking.setId(results.getInt("roomBookingId"));
			roomBooking.setCreateDate(new SimpleDateFormat("dd/MM/yyyy")
					.parse(results.getString("createDate")));
			roomBooking.setStartDate(new SimpleDateFormat("dd/MM/yyyy")
					.parse(results.getString("startDate")));
			roomBooking.setEndDate(new SimpleDateFormat("dd/MM/yyyy")
					.parse(results.getString("endDate")));
			roomBooking.setPayStatus(KeyWord.valueOf(results
					.getString("payStatus")));
			roomBooking.setRent(results.getDouble("rent"));
			roomBooking.setDeposit(results.getDouble("deposit"));

		} catch (Exception e) {
			System.out.println("error in building the roombooking object");
		}
		return roomBooking;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT roomBookingId, createDate, startDate, endDate, deposit, rent, payStatus FROM roomBooking";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

}
